set ansi_padding, ansi_warnings, concat_null_yields_null, arithabort, quoted_identifier, ansi_nulls on
go

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#tmpProperties')) drop table #tmpProperties
go

create table #tmpProperties ( name nvarchar(255), value nvarchar(255) )
go

insert into #tmpProperties values('script', 'update.0017.sql')
insert into #tmpProperties values('error', '0')

set xact_abort on
go

set transaction isolation level serializable
go

begin transaction
go

set ansi_nulls on
set quoted_identifier on   
go
/* BEGIN UPDATE SCRIPT */


if object_id('[dbo].[GetCustomerDetailsForInboundCall]') is not null drop proc [dbo].[GetCustomerDetailsForInboundCall]
go
create proc dbo.[GetCustomerDetailsForInboundCall] @customerId uniqueidentifier
as
	set rowcount 1
	select	a.*
	from	dbo.vwAccounts a
	where	a.id = @customerId
	union
	-- It may be case id
	select	a.*
	from	dbo.vwAccounts a
			inner join dbo.cases c on c.account_id = a.id
	where	c.id = @customerId
go




/* END UPDATE SCRIPT */
go
if @@error<>0 and @@trancount>0 rollback transaction
go

if @@trancount=0 begin
	update #tmpProperties set value = 1 where name = 'error'
	begin transaction
end
go

if (select top 1 value from #tmpProperties where name = 'error') <> '0' rollback transaction
go

if @@trancount > 0 begin
	declare @@scriptName nvarchar(255)
	select top 1 @@scriptName = value from #tmpProperties where name = 'script'

	if dbo.scriptExisted(@@scriptName) = 1 begin
		rollback transaction
		print @@scriptName + ' already executes'
	end
	else begin
		exec dbo.updateScript @@scriptName
		commit transaction
		print 'The database update succeeded'
	end
END
else begin
	print 'The database update failed'
end
go

drop table #tmpProperties
go
